package com.biostime.rpc.terminal.repository.impl;

import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.biostime.rpc.terminal.repository.TerminalVoRepository;
import com.biostime.rpc.terminal.thrift.inout.Location;
import com.mama100.common.repository.impl.BaseRepositoryImpl;

/**
 * 类功能描述: 线下门店持久层
 *
 * @version 1.0
 * @author zhuhaitao
 * @createDate 2015年12月21日 下午3:10:40
 */
@Repository
public class TerminalVoRepositoryImpl extends BaseRepositoryImpl implements TerminalVoRepository {
	@Autowired
	@Override
	@Qualifier("sessionFactory")
	public void initSessionFactory(SessionFactory sessionFactory) {
		this.setSessionFactory(sessionFactory);
	}

	/**
	 * 查询附近门店
	 */
	@Override
	public List<Map<String, Object>> findNearTerminals(Location location, Long distance, String terminalCode, String terminalName, int isStaff) {
		return getHibernateTemplate().execute(new HibernateCallback<List<Map<String, Object>>>() {
			@Override
			public List<Map<String, Object>> doInHibernate(Session session) throws HibernateException {
				StringBuffer sql = new StringBuffer();
				Double floating = 0.03;
				int i = (int) Math.floor((distance != null ? distance : 10000) / 10000);
				Double rangeFloating = (floating * i) < 0.05 ? 0.03 : (floating * i);

				sql.append("select t.*");
				sql.append("  from (select");
				sql.append(" /*+RULE*/");
				sql.append(" g.terminal_code,");
				sql.append(" order_distribute.calculate_length(");
				sql.append(location.getLng());
				sql.append(",");
				sql.append(location.getLat());
				sql.append(",g.longitude,g.latitude) as distance,");
				sql.append(" ct.code,");
				sql.append(" ct.name,");
				sql.append(" ct.status_code statusCode,");
				sql.append(" ct.terminal_channel_code terminalChannelCode,");
				sql.append(" ct.business_channel_code businessChannelCode,");
				sql.append(" ct.terminaltype,");
				sql.append(" ct.address,");
				sql.append(" ct.phone,");
				sql.append(" ct.qq,");
				sql.append(" ct.remark,");
				sql.append(" ct.remind,");
				sql.append(" ct.email,");
				sql.append(" ct.fax,");
				sql.append(" ct.short_name shortName,");
				sql.append(" ct.department_code departmentCode,");
				sql.append(" g.longitude,");
				sql.append(" g.latitude");
				sql.append("  from crm_terminal ct left join hyt_terminal_geo g on ct.code = g.terminal_code ");
				sql.append(" where ct.status_code = '01'");
				sql.append("   and ct.terminaltype in ('1', '2', '3')");
				sql.append("   and nvl(ct.terminal_type, 1) <> 0");
				if (null != terminalName && !"".equals(terminalName)) {
					sql.append(" and ct.name like '%").append(terminalName).append("%'");
				}
				if (null != terminalCode && !"".equals(terminalCode)) {
					sql.append(" and ct.code = '").append(terminalCode).append("'");
				} else {
					sql.append(" and g.longitude>= " + (Double.valueOf(location.getLng()) - rangeFloating));
					sql.append(" and g.longitude<=" + (Double.valueOf(location.getLng()) + rangeFloating));
					sql.append(" and g.latitude>= " + (Double.valueOf(location.getLat()) - rangeFloating));
					sql.append(" and g.latitude<=" + (Double.valueOf(location.getLat()) + rangeFloating));
				}
				sql.append(" order by distance nulls last) t");
				sql.append(" where 1=1 ");
				if (distance > 0 && (null == terminalCode || "".equals(terminalCode))) {
					sql.append(" and t.distance < " + distance);
				}
				sql.append("   and not exists (select 1");
				sql.append("          from mama100_owner.common_virual_terminal v");
				sql.append("         where v.terminal_code = t.terminal_code)");

				/**
				 * 如果是内部员工则展示内购店
				 */
				// if (isStaff == 1) {
				// sql.append(" union (select");
				// sql.append("       /*+RULE*/");
				// sql.append("        g.terminal_code,");
				// sql.append(" order_distribute.calculate_length(");
				// sql.append(location.getLng());
				// sql.append(",");
				// sql.append(location.getLat());
				// sql.append(",g.longitude,g.latitude) as distance,");
				// sql.append("        ct.code,");
				// sql.append("        ct.name,");
				// sql.append("        ct.status_code statusCode,");
				// sql.append("        ct.terminal_channel_code terminalChannelCode,");
				// sql.append("        ct.business_channel_code businessChannelCode,");
				// sql.append("        ct.terminaltype,");
				// sql.append("        ct.address,");
				// sql.append("        ct.phone,");
				// sql.append("        ct.qq,");
				// sql.append("        ct.remark,");
				// sql.append("        ct.remind,");
				// sql.append("        ct.email,");
				// sql.append("        ct.fax,");
				// sql.append("        ct.short_name shortName,");
				// sql.append("        ct.department_code departmentCode,");
				// sql.append("        g.longitude,");
				// sql.append("        g.latitude");
				// sql.append("         from crm_terminal ct, hyt_terminal_geo g");
				// sql.append("        where ct.code = g.terminal_code");
				// sql.append("          and ct.code = '912231')");
				// }
				SQLQuery query = session.createSQLQuery(sql.toString());
				return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			}
		});
	}
}
